﻿CREATE PROCEDURE [util].[ReverseEngineerAssembly]
@DatabaseName [sysname], @AssemblyName [sysname], @OutputPath NVARCHAR (256)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int
	SET @RC = 0
	
	DECLARE @RemoteQuery nvarchar(max)
	SET @RemoteQuery = N'
	SELECT @AssemblyContent = [content]
	FROM [DBADb].sys.assembly_files
	WHERE ([name] = @AssemblyName)';
	
	SET @RemoteQuery = REPLACE(@RemoteQuery,'DBADb',@DatabaseName);
	DECLARE @AssemblyContent VARBINARY(MAX)
	EXEC @RC = sp_executesql @RemoteQuery
		,N'@AssemblyName sysname, @AssemblyContent VARBINARY(MAX) OUTPUT'
		,@AssemblyName, @AssemblyContent OUTPUT;
	
	DECLARE @hObj int, @OAProc varchar(50), @hr int, @StepDesc varchar(100)

	SET @OAProc = 'sp_OACreate'
	
	SET @StepDesc = 'Creating the ADODB.Stream Object'
	PRINT @StepDesc
	EXEC @hr = sp_OACreate 'ADODB.Stream', @hObj OUT
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @OAProc = 'sp_OASetProperty'

	SET @StepDesc = 'Configuring binary stream type'
	PRINT @StepDesc
	DECLARE @adTypeBinary int
	SET @adTypeBinary = 1
	EXEC @hr = sp_OASetProperty @hObj, 'Type', @adTypeBinary
	IF (@hr <> 0) GOTO ERROR_HANDLER
	
	SET @OAProc = 'sp_OAMethod'
	
	SET @StepDesc = 'Opening stream'
	PRINT @StepDesc
	EXEC @hr = sp_OAMethod @hObj, 'Open', NULL
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Writing data to stream'
	PRINT @StepDesc
	EXEC @hr = sp_OAMethod @hObj, 'Write', NULL, @AssemblyContent
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Saving stream to file'
	PRINT @StepDesc
	DECLARE @adSaveCreateOverWrite int
	SET @adSaveCreateOverWrite = 2
	EXEC @hr = sp_OAMethod @hObj, 'SaveToFile', NULL, @OutputPath, @adSaveCreateOverWrite
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Closing stream'
	PRINT @StepDesc
	EXEC @hr = sp_OAMethod @hObj, 'Close', NULL
	IF (@hr <> 0) GOTO ERROR_HANDLER

	GOTO EXIT_PROC

ERROR_HANDLER:
	SET @RC = 1

	DECLARE @Source varchar(255), @Desc varchar(500)
	EXEC @hr = sp_OAGetErrorInfo NULL, @Source OUT, @Desc OUT
	IF (@hr <> 0)
		SELECT @Source = 'sp_OAGetErrorInfo', @Desc = 'Failed'

EXIT_PROC:
	IF (@hObj IS NOT NULL)
	AND(@OAProc <> 'sp_OADestroy')
	BEGIN
		SET @OAProc = 'sp_OADestroy'
		SET @StepDesc = 'Destroying ADODB.Stream Object'
		PRINT @StepDesc
		EXEC @hr = sp_OADestroy @hObj
		IF(@hr <> 0) GOTO ERROR_HANDLER
	END

	RETURN(@RC)
END